﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace PolePosition.v3.EntityFactory
{
    public sealed class KMLFactory
    {
        public static string GetUtilityKMLString(int? utilityId, int? userId )
        {
            System.Data.SqlClient.SqlCommand _SqlCommand;
            _SqlCommand = new System.Data.SqlClient.SqlCommand();
            _SqlCommand.CommandText = "up_KML_Utility";

            _SqlCommand.Parameters.AddWithValue("@UtilityId", utilityId.HasValue ? utilityId.Value : 0 );
            _SqlCommand.Parameters.AddWithValue("@UserId", userId.HasValue ? userId.Value : 0);

            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(_SqlCommand); ;
        }

        public static string GetKMLString(
            string transmissionStructuresList, string transmissionStructuresIcon, string transmissionStructuresColor, string transmissionStructuresText,
            string distributionPolesList, string distributionPolesIcon, string distributionPolesColor, string distributionPolesText,
            string metersList, string metersIcon, string metersColor, string metersText,
            string transformersList, string transformersIcon, string transformersColor, string transformersText,
            string transmissionGpsVisitsList, string transmissionGpsVisitsIcon, string transmissionGpsVisitsColor, string transmissionGpsVisitsText,
            string transmissionInspectionVisitsList, string transmissionInspectionVisitsIcon, string transmissionInspectionVisitsColor, string transmissionInspectionVisitsText,
            string distributionGpsVisitsList, string distributionGpsVisitsIcon, string distributionGpsVisitsColor, string distributionGpsVisitsText,
            string distributionInspectionVisitsList, string distributionInspectionVisitsIcon, string distributionInspectionVisitsColor, string distributionInspectionVisitsText,
        int? top)
        {

            System.Data.SqlClient.SqlCommand _SqlCommand;
            _SqlCommand = new System.Data.SqlClient.SqlCommand();
            _SqlCommand.CommandText = "up_KML";

            // add parameters (always translate empty strings to nulls)

            _SqlCommand.Parameters.AddWithValue("@TranmissionStructuresList", string.IsNullOrEmpty(transmissionStructuresList) ? null : transmissionStructuresList);
            _SqlCommand.Parameters.AddWithValue("@TranmissionStructuresIcon", string.IsNullOrEmpty(transmissionStructuresIcon) ? null : transmissionStructuresIcon);
            _SqlCommand.Parameters.AddWithValue("@TranmissionStructuresColor", string.IsNullOrEmpty(transmissionStructuresColor) ? null : transmissionStructuresColor);
            _SqlCommand.Parameters.AddWithValue("@TranmissionStructuresText", string.IsNullOrEmpty(transmissionStructuresText) ? "Key" : transmissionStructuresText);
            _SqlCommand.Parameters.AddWithValue("@DistributionPolesList", string.IsNullOrEmpty(distributionPolesList) ? null : distributionPolesList);
            _SqlCommand.Parameters.AddWithValue("@DistributionPolesIcon", string.IsNullOrEmpty(distributionPolesIcon) ? null : distributionPolesIcon);
            _SqlCommand.Parameters.AddWithValue("@DistributionPolesColor", string.IsNullOrEmpty(distributionPolesColor) ? null : distributionPolesColor);
            _SqlCommand.Parameters.AddWithValue("@DistributionPolesText", string.IsNullOrEmpty(distributionPolesText) ? "Key" : distributionPolesText);
            _SqlCommand.Parameters.AddWithValue("@MetersList", string.IsNullOrEmpty(metersList) ? null : metersList);
            _SqlCommand.Parameters.AddWithValue("@MetersIcon", string.IsNullOrEmpty(metersIcon) ? null : metersIcon);
            _SqlCommand.Parameters.AddWithValue("@MetersColor", string.IsNullOrEmpty(metersColor) ? null : metersColor);
            _SqlCommand.Parameters.AddWithValue("@MetersText", string.IsNullOrEmpty(metersText) ? "Key" : metersText);
            _SqlCommand.Parameters.AddWithValue("@TransformersList", string.IsNullOrEmpty(transformersList) ? null : transformersList);
            _SqlCommand.Parameters.AddWithValue("@TransformersIcon", string.IsNullOrEmpty(transformersIcon) ? null : transformersIcon);
            _SqlCommand.Parameters.AddWithValue("@TransformersColor", string.IsNullOrEmpty(transformersColor) ? null : transformersColor);
            _SqlCommand.Parameters.AddWithValue("@TransformersText", string.IsNullOrEmpty(transformersText) ? "Key" : transformersText);
            _SqlCommand.Parameters.AddWithValue("@TransmissionGpsVisitsList", string.IsNullOrEmpty(transmissionGpsVisitsList) ? null : transmissionGpsVisitsList);
            _SqlCommand.Parameters.AddWithValue("@TransmissionGpsVisitsIcon", string.IsNullOrEmpty(transmissionGpsVisitsIcon) ? null : transmissionGpsVisitsIcon);
            _SqlCommand.Parameters.AddWithValue("@TransmissionGpsVisitsColor", string.IsNullOrEmpty(transmissionGpsVisitsColor) ? null : transmissionGpsVisitsColor);
            _SqlCommand.Parameters.AddWithValue("@TransmissionGpsVisitsText", string.IsNullOrEmpty(transmissionGpsVisitsText) ? "Key" : transmissionGpsVisitsText);
            _SqlCommand.Parameters.AddWithValue("@TransmissionInspectionVisitsList", string.IsNullOrEmpty(transmissionInspectionVisitsList) ? null : transmissionInspectionVisitsList);
            _SqlCommand.Parameters.AddWithValue("@TransmissionInspectionVisitsIcon", string.IsNullOrEmpty(transmissionInspectionVisitsIcon) ? null : transmissionInspectionVisitsIcon);
            _SqlCommand.Parameters.AddWithValue("@TransmissionInspectionVisitsColor", string.IsNullOrEmpty(transmissionInspectionVisitsColor) ? null : transmissionInspectionVisitsColor);
            _SqlCommand.Parameters.AddWithValue("@TransmissionInspectionVisitsText", string.IsNullOrEmpty(transmissionInspectionVisitsText) ? "Key" : transmissionInspectionVisitsText);
            _SqlCommand.Parameters.AddWithValue("@DistributionGpsVisitsList", string.IsNullOrEmpty(distributionGpsVisitsList) ? null : distributionGpsVisitsList);
            _SqlCommand.Parameters.AddWithValue("@DistributionGpsVisitsIcon", string.IsNullOrEmpty(distributionGpsVisitsIcon) ? null : distributionGpsVisitsIcon);
            _SqlCommand.Parameters.AddWithValue("@DistributionGpsVisitsColor", string.IsNullOrEmpty(distributionGpsVisitsColor) ? null : distributionGpsVisitsColor);
            _SqlCommand.Parameters.AddWithValue("@DistributionGpsVisitsText", string.IsNullOrEmpty(distributionGpsVisitsText) ? "Key" : distributionGpsVisitsText);
            _SqlCommand.Parameters.AddWithValue("@DistributionInspectionVisitsList", string.IsNullOrEmpty(distributionInspectionVisitsList) ? null : distributionInspectionVisitsList);
            _SqlCommand.Parameters.AddWithValue("@DistributionInspectionVisitsIcon", string.IsNullOrEmpty(distributionInspectionVisitsIcon) ? null : distributionInspectionVisitsIcon);
            _SqlCommand.Parameters.AddWithValue("@DistributionInspectionVisitsColor", string.IsNullOrEmpty(distributionInspectionVisitsColor) ? null : distributionInspectionVisitsColor);
            _SqlCommand.Parameters.AddWithValue("@DistributionInspectionVisitsText", string.IsNullOrEmpty(distributionInspectionVisitsText) ? "Key" : distributionInspectionVisitsText);
            _SqlCommand.Parameters.AddWithValue("@Top", top.HasValue ? top : null);

            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(_SqlCommand); ;
        }


 
        public static string GetMapSearchCriteriaKMLString(
            string xMissionText, string distributionText,
            string xMissionGpsProjectList, string xMissionInspectionProjectList, string distributionInspectionProjectList, string distributionGpsProjectList, bool? xMissionAssets, bool? distributionAssets, bool? meterAssets, bool? transformerAssets, int? utilityFk, string mapList, string sectionList, string xMissionKvList, string xMissionTopsList, string xMissionSpeciesList, string xMissionTreatmentList, string xMissionClassList, string xMissionAgeList, string distributionPhaseList, string distributionTopsList, string distributionSpeciesList, string distributionTreatmentList, string distributionClassList, string distributionAgeList, string meterNotesList, bool? electicalProblem, string inspectionResultList, string inspectionTypeList, string inspectionVendorList, string inspectionCompanyList, string inspectionNoteList)
        {
            System.Data.SqlClient.SqlCommand _SqlCommand;
            _SqlCommand = new System.Data.SqlClient.SqlCommand();
            _SqlCommand.CommandText = "up_Map_SearchCriteriaIdLists";

            // add parameters (always translate empty strings to nulls)
            _SqlCommand.Parameters.AddWithValue("@XmissionGpsProjectList", string.IsNullOrEmpty(xMissionGpsProjectList) ? null : xMissionGpsProjectList);
            _SqlCommand.Parameters.AddWithValue("@XmissionInspectionProjectList", string.IsNullOrEmpty(xMissionInspectionProjectList) ? null : xMissionInspectionProjectList);
            _SqlCommand.Parameters.AddWithValue("@DistributionInspectionProjectList", string.IsNullOrEmpty(distributionInspectionProjectList) ? null : distributionInspectionProjectList);
            _SqlCommand.Parameters.AddWithValue("@DistributionGpsProjectList", string.IsNullOrEmpty(distributionGpsProjectList) ? null : distributionGpsProjectList);
            _SqlCommand.Parameters.AddWithValue("@XmissionAssets", xMissionAssets);
            _SqlCommand.Parameters.AddWithValue("@DistributionAssets", distributionAssets);
            _SqlCommand.Parameters.AddWithValue("@MeterAssets", meterAssets);
            _SqlCommand.Parameters.AddWithValue("@TransformerAssets", transformerAssets);
            _SqlCommand.Parameters.AddWithValue("@UtilityFk", utilityFk);
            _SqlCommand.Parameters.AddWithValue("@MapList", string.IsNullOrEmpty(mapList) ? null : mapList);
            _SqlCommand.Parameters.AddWithValue("@SectionList", string.IsNullOrEmpty(sectionList) ? null : sectionList);
            _SqlCommand.Parameters.AddWithValue("@XmissionKvList", string.IsNullOrEmpty(xMissionKvList) ? null : xMissionKvList);
            _SqlCommand.Parameters.AddWithValue("@XmissionTopsList", string.IsNullOrEmpty(xMissionTopsList) ? null : xMissionTopsList);
            _SqlCommand.Parameters.AddWithValue("@XmissionSpeciesList", string.IsNullOrEmpty(xMissionSpeciesList) ? null : xMissionSpeciesList);
            _SqlCommand.Parameters.AddWithValue("@XmissionTreatmentList", string.IsNullOrEmpty(xMissionTreatmentList) ? null : xMissionTreatmentList);
            _SqlCommand.Parameters.AddWithValue("@XmissionClassList", string.IsNullOrEmpty(xMissionClassList) ? null : xMissionClassList);
            _SqlCommand.Parameters.AddWithValue("@XmissionAgeList", string.IsNullOrEmpty(xMissionAgeList) ? null : xMissionAgeList);
            _SqlCommand.Parameters.AddWithValue("@DistributionPhaseList", string.IsNullOrEmpty(distributionPhaseList) ? null : distributionPhaseList);
            _SqlCommand.Parameters.AddWithValue("@DistributionTopsList", string.IsNullOrEmpty(distributionTopsList) ? null : distributionTopsList);
            _SqlCommand.Parameters.AddWithValue("@DistributionSpeciesList", string.IsNullOrEmpty(distributionSpeciesList) ? null : distributionSpeciesList);
            _SqlCommand.Parameters.AddWithValue("@DistributionTreatmentList", string.IsNullOrEmpty(distributionTreatmentList) ? null : distributionTreatmentList);
            _SqlCommand.Parameters.AddWithValue("@DistributionClassList", string.IsNullOrEmpty(distributionClassList) ? null : distributionClassList);
            _SqlCommand.Parameters.AddWithValue("@DistributionAgeList", string.IsNullOrEmpty(distributionAgeList) ? null : distributionAgeList);
            _SqlCommand.Parameters.AddWithValue("@MeterNotesList", string.IsNullOrEmpty(meterNotesList) ? null : meterNotesList);
            _SqlCommand.Parameters.AddWithValue("@ElecticalProblem", electicalProblem);
            _SqlCommand.Parameters.AddWithValue("@InspectionResultList", string.IsNullOrEmpty(inspectionResultList) ? null : inspectionResultList);
            _SqlCommand.Parameters.AddWithValue("@InspectionTypeList", string.IsNullOrEmpty(inspectionTypeList) ? null : inspectionTypeList);
            _SqlCommand.Parameters.AddWithValue("@InspectionVendorList", string.IsNullOrEmpty(inspectionVendorList) ? null : inspectionVendorList);
            _SqlCommand.Parameters.AddWithValue("@InspectionCompanyList", string.IsNullOrEmpty(inspectionCompanyList) ? null : inspectionCompanyList);
            _SqlCommand.Parameters.AddWithValue("@InspectionNoteList", string.IsNullOrEmpty(inspectionNoteList) ? null : inspectionNoteList);

            // execute
            System.Data.DataSet _DataSet;
            _DataSet = ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(_SqlCommand);

            string _Kml = null;
            if (_DataSet == null || _DataSet.Tables.Count == 0 || _DataSet.Tables[0].Rows.Count == 0)
                return _Kml;

            string _TransmissionStructuresList = null;
            string _DistributionPolesList = null;
            string _MetersList = null;
            string _TransformersList = null;
            string _TransmissionGpsVisitsList = null;
            string _TransmissionInspectionVisitsList = null;
            string _DistributionGpsVisitsList = null;
            string _DistributionInspectionVisitsList = null;

            _TransmissionStructuresList = _DataSet.Tables[0].Rows[0].Field<string>("XmissionAssetIdList");
            _DistributionPolesList = _DataSet.Tables[0].Rows[0].Field<string>("DistributionAssetIdList"); ;
            _MetersList = _DataSet.Tables[0].Rows[0].Field<string>("MeterAssetIdList");
            _TransformersList = _DataSet.Tables[0].Rows[0].Field<string>("TransformerAssetIdList");
            _TransmissionGpsVisitsList = _DataSet.Tables[0].Rows[0].Field<string>("XmissionGPSVisitIdList"); ;
            _TransmissionInspectionVisitsList = _DataSet.Tables[0].Rows[0].Field<string>("XmissionInspectionVisitIdList"); ;
            //_DistributionGpsVisitsList = _DataSet.Tables[0].Rows[0].Field<string>("");;
            _DistributionInspectionVisitsList = _DataSet.Tables[0].Rows[0].Field<string>("DistibutionInspectionVisitIdList"); ;

            _Kml = GetKMLString(
                    
                    _TransmissionStructuresList, null, null, xMissionText,
                    _DistributionPolesList, null, null, distributionText,
                    _MetersList, null, null, null,
                    _TransformersList, null, null, null,
                    _TransmissionGpsVisitsList, null, null, xMissionText,
                    _TransmissionInspectionVisitsList, null, null, xMissionText,
                    _DistributionGpsVisitsList, null, null, distributionText,
                    _DistributionInspectionVisitsList, null, null, distributionText,
                    null);

            return _Kml;
        }
    }
}
